package henu.dao.impl;

import henu.bean.Content;
import henu.dao.ContentDao;
import henu.dao.factory.DaoFactory;

import java.sql.SQLException;
import java.util.List;

import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;

public class ContentDaoImpl implements ContentDao{

	/*public static void main(String[] args){
		ContentDaoImpl scp = new ContentDaoImpl();
		Content sc = new Content();
		sc.setSid(15);
		sc.setSeid(132);
		sc.setTitle("yuchen");
		sc.setContent("anquanjiaoyu");
		sc.setEdutype("视频");
		sc.setPublishtime("ww");
		sc.setPublisher("oo");
		sc.setViewnumbers("ww");
		sc.setMemo("ww");
		System.out.println(scp.save(sc));
		System.out.println(scp.findByProperty("sid", "123", "sid", "desc", 0, 0));
		
	}*/
	
		
	@Override
	public int save(Content spe) {
		QueryRunner runner = DaoFactory.getRunner();
		int result = 0;
		String sql = "INSERT INTO SAFETYEDUCATION VALUES(?,?,?,?,?,?,?,?,?)";
		Object[] params = { spe.getSid(),spe.getSeid(),spe.getTitle(),spe.getContent(),spe.getEdutype(),
				spe.getPublishtime(),spe.getPublisher(),
							spe.getViewnumbers(),spe.getMemo()};
		try {
			result = runner.update(sql,params);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return result;
	}

	@Override
	public int delete(String sId) {
		String sql = "DELETE FROM SAFETYEDUCATION where sid=?";
		int result = 0;
		QueryRunner runner = DaoFactory.getRunner();
		try {
			result = runner.update(sql,sId);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return result;
				
	}

	@Override
	public int update(String sId, Content spe) {
		int result = 0;
		
		try {
			QueryRunner runner = DaoFactory.getRunner();
			String sql = "UPDATE SAFETYEDUCATION SET seid=?,title=?,content=?,edutype=?,publisher=?,publishtime=?,memo=? WHERE sid=?";
			Object[] params = {spe.getSeid(),spe.getTitle(),spe.getContent(),
					spe.getEdutype(),spe.getPublisher(),spe.getPublishtime(),
					spe.getMemo(),sId};
			result = runner.update(sql,params);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return result;
	}

	@Override
	public List<Content> findAll(String order, String sort) {
		List <Content> list = null;
		QueryRunner runner = DaoFactory.getRunner();
		String sql = "select * from safetyeducation  order by " + order + " " + sort + "";
		try {
			list = runner.query(sql, new BeanListHandler<Content>(Content.class));
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return list;
	}

	@Override
	public List<Content> findByProperty(String property, String key,
			String order, String sort, int start, int end) {
			List<Content> list= null;
			QueryRunner runner = DaoFactory.getRunner();
			String sql = "SELECT * FROM (SELECT tt.*, ROWNUM ro FROM (select * from SAFETYEDUCATION where "+property+"='"+key+"'  order by "+order+" "+sort+") tt WHERE ROWNUM <="+end+") WHERE ro > "+start+"";
			try {
				list = runner.query(sql, new BeanListHandler<Content>(Content.class));
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
			
			
			return list;
	}


	@Override
	public Content findById(String sId) {
		// TODO Auto-generated method stub
		Content spe = null;
		String sql = "select * from SAFETYEDUCATION where sid=?";
		QueryRunner runner = DaoFactory.getRunner();
		try {
			spe = runner.query(sql,new BeanHandler<Content>(Content.class), sId);
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return spe;
	}
	public List<Content> findByDetial(String sId) {
		// TODO Auto-generated method stub
		List <Content> list = null;
		String sql = "select * from SAFETYEDUCATION where seid=?";
		QueryRunner runner = DaoFactory.getRunner();
		try {
			list = runner.query(sql, new BeanListHandler<Content>(Content.class),sId);
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return list;
	}

	@Override
	public void updateViewNumberAddOne(String uid) {
		// TODO Auto-generated method stub
		String sql = "UPDATE SAFETYEDUCATION SET VIEWNUMBERS=(VIEWNUMBERS+1) where sid=?";
		int result = 0;
		QueryRunner runner = DaoFactory.getRunner();
		try {
			result = runner.update(sql,uid);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}	
		System.out.println("");
	}

	@Override
	public List<Content> findByProperty(String property, String key,
			String seid, String uid, String order,
			String sort, int start, int end) {
		List <Content> list = null;
		QueryRunner runner = DaoFactory.getRunner();
		String sql ="SELECT * FROM (SELECT tt.*, ROWNUM ro FROM (select * from SAFETYEDUCATION where "+property+"='"+key+"' and seid='"+uid+"' order by "+order+" "+sort+") tt WHERE ROWNUM <="+end+") WHERE ro > "+start+"";
		try {
			list = runner.query(sql, new BeanListHandler<Content>(Content.class));
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return list;
	}
	


	
	
}
